# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
from lxml import etree
import sqlite3
import base64

class VideoPipeline(object):
    def __init__(self):
        self.conn = sqlite3.connect("video.db")
        query = """create table IF NOT EXISTS tv(ID INTEGER PRIMARY KEY   AUTOINCREMENT,
                videoname TEXT    NOT NULL,
                img TEXT,
                otherdata VARCHAR(100),
                director  VARCHAR(60),
                area VARCHAR(40),
                decade  VARCHAR(40),
                category VARCHAR(40),
                Starring  VARCHAR(100),
                Introduction  TEXT,
                urldata  TEXT );"""
        self.conn.execute(query)
        query = """create table IF NOT EXISTS movie(ID INTEGER PRIMARY KEY   AUTOINCREMENT,
                videoname TEXT    NOT NULL,
                img TEXT,
                otherdata VARCHAR(100),
                director  VARCHAR(60),
                area VARCHAR(40),
                decade  VARCHAR(40),
                category VARCHAR(40),
                Starring  VARCHAR(100),
                Introduction  TEXT,
                urldata  TEXT );"""
        self.conn.execute(query)
        query = """create table IF NOT EXISTS tvshow(ID INTEGER PRIMARY KEY   AUTOINCREMENT,
                videoname TEXT    NOT NULL,
                img TEXT,
                otherdata VARCHAR(100),
                director  VARCHAR(60),
                area VARCHAR(40),
                decade  VARCHAR(40),
                category VARCHAR(40),
                Starring  VARCHAR(100),
                Introduction  TEXT,
                urldata  TEXT );"""
        self.conn.execute(query)
        query = """create table IF NOT EXISTS comic(ID INTEGER PRIMARY KEY   AUTOINCREMENT,
                videoname TEXT    NOT NULL,
                img TEXT,
                otherdata VARCHAR(100),
                director  VARCHAR(60),
                area VARCHAR(40),
                decade  VARCHAR(40),
                category VARCHAR(40),
                Starring  VARCHAR(100),
                Introduction  TEXT,
                urldata  TEXT );"""
        self.conn.execute(query)
        self.conn.commit()
    def close_spider(self,spider):
        self.conn.close()
    def process_item(self, item, spider):
        if item["type"]=="1":
            query = """insert into tv(videoname,img,otherdata,director,area,decade,category,\
            Starring,Introduction,urldata)VALUES("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")"""
        elif item["type"]=="2":
            query = """insert into movie(videoname,img,otherdata,director,area,decade,category,\
            Starring,Introduction,urldata)VALUES("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")"""
        elif item["type"]=="3":
            query = """insert into tvshow(videoname,img,otherdata,director,area,decade,category,\
            Starring,Introduction,urldata)VALUES("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")"""
        elif item["type"]=="4":
            query = """insert into comic(videoname,img,otherdata,director,area,decade,category,\
            Starring,Introduction,urldata)VALUES("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")"""
        self.conn.execute(query %(item["videoname"],item["img"],item["otherdata"],item["director"],\
                                  item["area"],item["decade"],item["category"],item["Starring"],item["Introduction"],\
                                  item["urldata"]))
        self.conn.commit()
        return item
